package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Class;
import model.Classlist;

public class ClassMod {
	Connection connection;
	private static ClassMod isntance;
	public static ClassMod getIsntance() {
		if (isntance == null) {
			isntance = new ClassMod();
		}
		return isntance;
	}
	public boolean addClass(Class class1) {
		PreparedStatement sqlr = null;
		boolean result = false;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql = "INSERT INTO class(clid , uid , idclass , nop , bio ) VALUES(? , ? , ? , ? , ?) ";
			sqlr = connection.prepareStatement(sql);
			sqlr.setInt(1, class1.getClid());
			sqlr.setInt(2, class1.getUid());
			sqlr.setString(3, class1.getIdclass());
			sqlr.setInt(4, class1.getNop());
			sqlr.setString(5, class1.getBio());
			int number = sqlr.executeUpdate();
			if (number > 0) {
				result = true;
			}
			sqlr.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}

	public boolean addStudentToClass(int sid, int clid) {
		PreparedStatement sqlr = null;
		boolean result = false;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql = "INSERT INTO classlist(clid , sid ) VALUES(? , ? ) ";
			sqlr = connection.prepareStatement(sql);
			sqlr.setInt(1, clid);
			sqlr.setInt(2, sid);
			int number = sqlr.executeUpdate();
			if (number > 0) {
				result = true;
			}
			sqlr.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}

	

	public boolean isExitClidInTable(int clid) {
		PreparedStatement sqls = null;
		boolean result = false;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			sqls = connection
					.prepareStatement("SELECT clid from classlist where clid = ?");
			sqls.setInt(1, clid);
			ResultSet rs = sqls.executeQuery();
			while (rs.next()) {
				result = true;
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
		}
		return result;
	}

	public boolean isClid(int clid) {
		PreparedStatement sqls = null;
		boolean result = false;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			sqls = connection
					.prepareStatement("SELECT clid from class where clid = ?");
			sqls.setInt(1, clid);
			ResultSet rs = sqls.executeQuery();
			while (rs.next()) {
				result = true;
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean delStudentInClass(int clid, int sid) {
		PreparedStatement delpd = null;
		boolean result = false;
		Connection connection = DB_Connection.getIsntance().getConnection();
		try {

			delpd = connection
					.prepareStatement("DELETE FROM classlist WHERE clid = ? AND sid = ? LIMIT 1");
			delpd.setInt(1, clid);
			delpd.setInt(1, sid);
			int number = delpd.executeUpdate();
			if (number > 0) {
				result = true;
			}
			delpd.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();

		}
		return result;
	}
	public boolean delAllStudentInClass(int clid) {
		PreparedStatement delpd = null;
		boolean result = false;
		Connection connection = DB_Connection.getIsntance().getConnection();
		try {

			delpd = connection
					.prepareStatement("DELETE FROM classlist WHERE clid = ? ");
			delpd.setInt(1, clid);
			int number = delpd.executeUpdate();
			if (number > 0) {
				result = true;
			}
			delpd.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean delClass(int clid) {
		PreparedStatement delpd = null;
		boolean result = false;
		Connection connection = DB_Connection.getIsntance().getConnection();
		if (isExitClidInTable(clid)) {
			if (!delAllStudentInClass(clid))
				return false;
		}
		try {
			delpd = connection
					.prepareStatement("DELETE FROM class WHERE clid = ? ");
			delpd.setInt(1, clid);
			int number = delpd.executeUpdate();
			if (number > 0) {
				result = true;
			}
			delpd.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public boolean UpDateClass(Class n, int clid) {
		PreparedStatement sqlr = null;
		boolean result = false;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql = "UPDATE class SET uid = ? , idclass = ? , nop = ? , bio = ? WHERE clid = ? LIMIT = 1 ";
			sqlr = connection.prepareStatement(sql);
			sqlr.setInt(1, n.getUid());
			sqlr.setString(2, n.getIdclass());
			sqlr.setInt(3, n.getNop());
			sqlr.setString(4, n.getBio());
			sqlr.setInt(5, clid);
			int number = sqlr.executeUpdate();
			if (number > 0) {
				result = true;
			}
			sqlr.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}

	public Class getClass(int clid) {
		PreparedStatement sqls = null;
		Class result = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql = "SELECT clid , uid , idclass , nop , bio FROM class WHERE clid = ?";
			sqls = connection.prepareStatement(sql);
			sqls.setInt(1, clid);
			ResultSet rs = sqls.executeQuery();
			while (rs.next()) {
				int cid = rs.getInt(1);
				int uid = rs.getInt(2);
				String idclass = rs.getString(3);
				int nop = rs.getInt(4);
				String bio = rs.getString(5);
				result = new Class(cid, uid, idclass, nop, bio);
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;

	}
	public ArrayList<Classlist> getClassList(int clid){
		PreparedStatement sqls = null;
		ArrayList<Classlist> result = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sql = "SELECT clid , sid FROM classlist WHERE clid = ?";
			sqls = connection.prepareStatement(sql);
			sqls.setInt(1, clid);
			ResultSet rs = sqls.executeQuery();
			while (rs.next()) {
				int cid = rs.getInt(1);
				int sid = rs.getInt(2);
				result.add(new Classlist(0, clid, sid));
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
}
